﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_DistributionInspVisitTransformerToAsset')
	DROP PROCEDURE up_DistributionInspVisitTransformerToAsset
GO

CREATE PROCEDURE [dbo].[up_DistributionInspVisitTransformerToAsset]
(
	@vendorProjectId	int,
	@feets				int
)
AS

/*
SELECT * FROM tb_Utility_Asset_Transformers
SELECT * FROM tb_Vendor_Distribution_Transformers

DELETE tb_Utility_Asset_Transformers
UPDATE tb_Vendor_Distribution_Transformers SET utility_asset_transformers_transformerfk = NULL

DBCC CHECKIDENT (tb_Utility_Asset_Transformers, RESEED, 0)
*/


--Get Ids
DECLARE @utilityId int
SELECT 
	@utilityId = UtilityFk
FROM
	tb_Vendor_projects
WHERE
	Vendor_ProjectId = @vendorProjectId

/*
DECLARE @UtilityProjectId int
SELECT 
	@UtilityProjectId = uProject.Utility_ProjectId
FROM 
	tb_vendor_Projects vProject
INNER JOIN tb_utility_projects  uProject
	ON vProject.VendorFk = uProject.VendorFk
	AND vProject.ProjectTypeFk = uProject.TypeFk
WHERE  
	vProject.Vendor_ProjectId = @vendorProjectId
*/
DECLARE @insertedAssetIds TABLE (Utility_Asset_TransformerId int, TransformerId int)

UPDATE 
	transformers
SET 
	transformers.Utility_Asset_Transformers_TransformerFk = assets.Utility_Asset_TransformerId
FROM 
	tb_Vendor_Distribution_Transformers transformers
INNER JOIN tb_Vendor_Distribution_Poles poles
	ON transformers.Vendor_Distribution_PoleFk = poles.Vendor_Distribution_Poles_PoleId
INNER JOIN tb_vendor_projects vendorProjects
	ON vendorProjects.Vendor_ProjectId = poles.ProjectFk
INNER JOIN tb_Utility_Asset_Transformers assets
	ON assets.UtilityFk = vendorProjects.UtilityFk
WHERE
	dbo.fn_DistanceBetween(transformers.Latitude, transformers.Longitude, assets.Latitude, assets.Longitude) < @feets
	AND poles.ProjectFk = @vendorProjectId
	AND transformers.Utility_Asset_Transformers_TransformerFk IS NULL


--Inserts asset meters
INSERT INTO tb_Utility_Asset_Transformers
(
	UtilityFK,
	Latitude,
	Longitude,
	Altitude,
	CreatedDate,
	Vendor_ProjectFk,
	SerialNumber,
	Comments,
	ImageFileName
)
OUTPUT INSERTED.Utility_Asset_TransformerId, INSERTED.Altitude INTO @insertedAssetIds
SELECT
	@utilityId,
	transformers.Latitude,
	transformers.Longitude,
	transformers.Vendor_Distribution_Transformers_TransformerId, -- TODO: Change this, Using Altitude as temp id	
	GETDATE(),
	@vendorProjectId,
	transformers.SerialNumber,
	transformers.Comments,
	transformers.ImageFileName
FROM
	tb_Vendor_Distribution_Poles poles
INNER JOIN tb_Vendor_Distribution_Transformers transformers
	ON transformers.Vendor_Distribution_PoleFk = poles.Vendor_Distribution_Poles_PoleId
WHERE
	poles.ProjectFk = @vendorProjectId
	AND transformers.Utility_Asset_Transformers_TransformerFk IS NULL


--Update inspection transformers to relate them to the asset
UPDATE 
	tb_Vendor_Distribution_Transformers
SET
	Utility_Asset_Transformers_TransformerFk = [InsertedIds].Utility_Asset_TransformerId
FROM 
	@insertedAssetIds [InsertedIds]
WHERE
	tb_Vendor_Distribution_Transformers.Vendor_Distribution_Transformers_TransformerId = [InsertedIds].TransformerId


--Update Vendor Project to right value
UPDATE 
	tb_Utility_Asset_Transformers
SET
	Altitude = NULL
FROM 
	tb_Utility_Asset_Transformers
INNER JOIN @insertedAssetIds [InsertedIds]
	ON tb_Utility_Asset_Transformers.Utility_Asset_TransformerId = [InsertedIds].Utility_Asset_TransformerId

/*
select * from tb_Utility_Asset_Meters_Images
*/
 